package com.huaxia.dao.product;

import com.huaxia.pojo.product.SaleDeptLuck;

import org.apache.ibatis.annotations.Select;


import java.util.List;

/**
 * @author wangli 爱我华夏，爱我中华！
 * @date 2020/06/10 14:14
 */

public interface SaleDeptLuckMapper {

 @Select("select a.SALEDEPTSNAME,a.saleDeptAllAgent,b.saleDeptPreNum,c.saleDeptLuckNum,round(decode(b.SALEDEPTPreNum,0,0,c.SALEDEPTLuckNum*100/b.SALEDEPTPreNum),1) preLuckYield,\n" +
         "d.SALEDEPTLuckSure as saleDeptLuckSure,e.SALEDEPTMonSure as saleDeptLuckSure ,round(decode(e.SALEDEPTMonSure,0,0,d.SALEDEPTLuckSure*100/e.SALEDEPTMonSure),1)saleDeptLuckYield from\n" +
         "(select SB.SALEDEPTSNAME,NVL(a.SALEDEPTAllAgent,0)SALEDEPTAllAgent from\n" +
         "(select t.SALEDEPTCODE,count(t.SALEDEPTCODE) SALEDEPTAllAgent from D_AGENT_PROVINCE_YX2 t where  AGENTSTATE='在职' and t.name <> '客户服务专员' group by t.SALEDEPTCODE)a right join SD_SALEDEPT sb on a.SALEDEPTCODE=sb.SALEDEPTCODE\n" +
         ")a,\n" +
         "(select sb.SALEDEPTsname,NVL(a.num,0)SALEDEPTPreNum from\n" +
         "(select a.SALEDEPTcode,count(a.agentcode)num from\n" +
         "(select t.SALEDEPTCODE, t.AGENTCODE,SUM(t.WRITTENSTADPREM)cb from FACT_YX_PREPREM_LIST_day t\n" +
         "where to_char(t.issue_date,'yyyy/mm')=to_char(sysdate,'yyyy/mm') and t.agentstate='在职' group by t.agentcode,t.SALEDEPTcode)a where a.cb>0 group by a.SALEDEPTcode)a\n" +
         "right join SD_SALEDEPT sb on sb.SALEDEPTcode=a.SALEDEPTcode\n" +
         ")b,\n" +
         "(select sb.SALEDEPTsname,NVL(a.num,0)SALEDEPTLuckNum  from(\n" +
         "select a.SALEDEPTcode,count(a.SALEDEPTcode)num from(select t.SALEDEPTcode from FACT_YX_PREPREM_LIST_day t\n" +
         "where to_char(t.issue_date,'yyyy/mm')=to_char(sysdate,'yyyy/mm') and t.agentstate='在职' and  (instr(t.PRODUCT_NAME,'福临门')>0 or instr(t.PRODUCT_NAME,'传家宝')>0) group by t.agentcode,t.SALEDEPTcode)a group by a.SALEDEPTcode)a right join SD_SALEDEPT sb on sb.SALEDEPTcode=a.SALEDEPTcode\n" +
         ")c,\n" +
         "(select sb.SALEDEPTsname,NVL(a.ycb,0)SALEDEPTLuckSure from\n" +
         "(select a.SALEDEPTCODE,NVL(a.cbm,0)-NVL(b.ytm,0) ycb from\n" +
         "(select t.SALEDEPTCODE, SUM(WRITTENSTADPREM)cbm from FACT_YX_PREPREM_LIST_day t\n" +
         "where to_char(t.ISSUE_DATE,'yyyy/mm')  = to_char(sysdate,'yyyy/mm') and  (instr(t.PRODUCT_NAME,'福临门')>0 or instr(t.PRODUCT_NAME,'传家宝')>0) and t.deptcode1 is not null\n" +
         "and t.RNFLAG='N'  group by t.SALEDEPTCODE)a left join\n" +
         "(select s.SALEDEPTCODE,SUM(WRITTENSTADPREM)ytm from FACT_YX_PREPREM_LIST_day s\n" +
         "where  to_char(s.yt_date,'yyyy/mm')  = to_char(sysdate,'yyyy/mm') and (instr(s.PRODUCT_NAME,'福临门')>0 or instr(s.PRODUCT_NAME,'传家宝')>0) and s.RNFLAG='N' and s.deptcode1 is not null\n" +
         "group by s.SALEDEPTCODE)b on a.SALEDEPTCODE=b.SALEDEPTCODE\n" +
         ")a right join SD_SALEDEPT sb on sb.SALEDEPTcode=a.SALEDEPTcode\n" +
         ")d,\n" +
         "(select sb.SALEDEPTsname,NVL(a.ycb,0)SALEDEPTMonSure from\n" +
         "(select a.SALEDEPTCODE,NVL(a.cbm,0)-NVL(b.ytm,0) ycb from\n" +
         "(select t.SALEDEPTCODE, SUM(WRITTENSTADPREM)cbm from FACT_YX_PREPREM_LIST_day t\n" +
         "where to_char(t.ISSUE_DATE,'yyyy/mm')  = to_char(sysdate,'yyyy/mm') and t.deptcode1 is not null\n" +
         "and t.RNFLAG='N'  group by t.SALEDEPTCODE)a left join\n" +
         "(select s.SALEDEPTCODE,SUM(WRITTENSTADPREM)ytm from FACT_YX_PREPREM_LIST_day s\n" +
         "where  to_char(s.yt_date,'yyyy/mm')  = to_char(sysdate,'yyyy/mm') and s.RNFLAG='N' and s.deptcode1 is not null\n" +
         "group by s.SALEDEPTCODE)b on a.SALEDEPTCODE=b.SALEDEPTCODE\n" +
         ")a right join SD_SALEDEPT sb on sb.SALEDEPTcode=a.SALEDEPTcode\n" +
         ")e where a.SALEDEPTSNAME=b.SALEDEPTSNAME and b.SALEDEPTSNAME=c.SALEDEPTSNAME and c.SALEDEPTSNAME=d.SALEDEPTSNAME and d.SALEDEPTSNAME=e.SALEDEPTSNAME ORDER by c.saleDeptLuckNum DESC")
 List<SaleDeptLuck> getSaleDeptLuck();
}
